﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.OleDb;
using System.Configuration;
using System.Windows.Forms.DataVisualization.Charting;

namespace WindowsFormsApplication1
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        private void Form1_Load(object sender, EventArgs e)
        {
            string excelconn = ConfigurationManager.ConnectionStrings["MSIDConn"].ConnectionString;
            OleDbConnection conn = new OleDbConnection();
            conn.ConnectionString = excelconn;

            OleDbCommand command1 = new OleDbCommand
                        (
                  "SELECT ID ,ParentID ,Title " +
                " FROM [ProductL1$]", conn
                        );
            DataSet ds1 = new DataSet();
            OleDbDataAdapter adaptor1 = new OleDbDataAdapter(command1);
            adaptor1.Fill(ds1);
            ProductL1.DataSource = ds1.Tables[0].DefaultView;
            ProductL1.ValueMember = "ID";
            ProductL1.DisplayMember = "Title";

            OleDbCommand command6 = new OleDbCommand
(
"SELECT ID	,Title " +
" FROM [Region$]", conn
);
            DataSet ds6 = new DataSet();
            OleDbDataAdapter adaptor6 = new OleDbDataAdapter(command6);
            adaptor6.Fill(ds6);
            regions.DataSource = ds6.Tables[0].DefaultView;
            regions.ValueMember = "ID";
            regions.DisplayMember = "Title";


          

        }

        private void ProductL1_SelectedIndexChanged(object sender, EventArgs e)
        {
            string excelconn = ConfigurationManager.ConnectionStrings["MSIDConn"].ConnectionString;
            OleDbConnection conn = new OleDbConnection();
            conn.ConnectionString = excelconn;


            OleDbCommand command2 = new OleDbCommand
                (
                "SELECT ID	,Parent_ID ,Title " +
                 " FROM [ProductL2$] ", conn
                );

            DataSet ds2 = new DataSet();
            OleDbDataAdapter adaptor2 = new OleDbDataAdapter(command2);
            adaptor2.Fill(ds2);
            ProductL2.DataSource = ds2.Tables[0].DefaultView;
            ProductL2.ValueMember = "ID";
            ProductL2.DisplayMember = "Title";


            if (ProductL1.ValueMember == "ID")
            {
                ((DataView)ProductL2.DataSource).RowFilter = "Parent_ID = " + ProductL1.SelectedValue;
            }
        }

        private void ProductL2_SelectedIndexChanged(object sender, EventArgs e)
        {
            string excelconn = ConfigurationManager.ConnectionStrings["MSIDConn"].ConnectionString;
            OleDbConnection conn = new OleDbConnection();
            conn.ConnectionString = excelconn;
            OleDbCommand command3 = new OleDbCommand
            (
            "SELECT ID	,ParentID ,Title " +
              " FROM [ProductL3$]  ", conn
             );
            DataSet ds3 = new DataSet();
            OleDbDataAdapter adaptor3 = new OleDbDataAdapter(command3);
            adaptor3.Fill(ds3);
            ProductL3.DataSource = ds3.Tables[0].DefaultView;
            ProductL3.ValueMember = "ID";
            ProductL3.DisplayMember = "Title";

            if (ProductL2.ValueMember == "ID")
                ((DataView)ProductL3.DataSource).RowFilter = "ParentID = " + ProductL2.SelectedValue;
        }

        private void ProductL3_SelectedIndexChanged(object sender, EventArgs e)
        {
            string excelconn = ConfigurationManager.ConnectionStrings["MSIDConn"].ConnectionString;
            OleDbConnection conn = new OleDbConnection();
            conn.ConnectionString = excelconn;

            OleDbCommand command4 = new OleDbCommand
                            (
                            "SELECT ID	,Title " +
                    " FROM [DT1$]", conn
                            );
            DataSet ds4 = new DataSet();
            OleDbDataAdapter adaptor4 = new OleDbDataAdapter(command4);
            adaptor4.Fill(ds4);
            DT1.DataSource = ds4.Tables[0].DefaultView;
            DT1.ValueMember = "ID";
            DT1.DisplayMember = "Title";
        }

        private void regions_SelectedIndexChanged(object sender, EventArgs e)
        {
            string excelconn = ConfigurationManager.ConnectionStrings["MSIDConn"].ConnectionString;
            OleDbConnection conn = new OleDbConnection();
            conn.ConnectionString = excelconn;
            OleDbCommand command7 = new OleDbCommand
            (
            "SELECT ID	,Region ,Title " +
    " FROM [Locations$]", conn
            );
            DataSet ds7 = new DataSet();
            OleDbDataAdapter adaptor7 = new OleDbDataAdapter(command7);
            adaptor7.Fill(ds7);
            locations.DataSource = ds7.Tables[0].DefaultView;
            locations.ValueMember = "ID";
            locations.DisplayMember = "Title";

            if (regions.ValueMember == "ID")
                ((DataView)locations.DataSource).RowFilter = "Region = " + regions.SelectedValue;

            
        }

        private void datagrid_update()
        {

            string excelconn = ConfigurationManager.ConnectionStrings["MSIDConn"].ConnectionString;
            OleDbConnection conn = new OleDbConnection();
            conn.ConnectionString = excelconn;

            DataRowView drv1 = (DataRowView)ProductL1.SelectedItem;
            String valP1 = drv1["ID"].ToString();

            DataRowView drv2 = (DataRowView)ProductL2.SelectedItem;
            String valP2 = drv2["ID"].ToString();

            DataRowView drv3 = (DataRowView)ProductL3.SelectedItem;
            String valP3 = drv3["ID"].ToString();

            OleDbCommand command9 = new OleDbCommand
      (
  " SELECT P1, P2, P3, Product_Type ,City ," +
  " CC_Volume  ,PD_Volume ,CC_Percent ,WE_Volume  ,WE_Percent ,ME_Volume  ,ME_Percent ,MTD_Growth ,YE_Volume  ,YE_Percent ,YTD_Growth , " +
  " (CC_Volume -PD_Volume) as 'CC-PD_Volume',	(CC_Volume-WE_Volume) as 'CC-WE_Volume',	(CC_Volume-YE_Volume )	as 'CC-YE_Volume' " +
  " FROM [PE_Actual$] where P1 = " + valP1 + " AND P2 = " + valP2 + " AND P3 = " +
      valP3 + " AND City = '" + locations.Text + "'"
   , conn
      );

            DataSet ds9 = new DataSet();
            OleDbDataAdapter adaptor9 = new OleDbDataAdapter(command9);
            adaptor9.Fill(ds9, "PE_Actual");
            dataGridView1.DataSource = ds9.Tables[0].DefaultView;
            chart1.DataSource = ds9.Tables[0].DefaultView;
            

            // textBox1.Text = vva;
            dataGridView1.Columns[0].Visible = false;
            dataGridView1.Columns[1].Visible = false;
            dataGridView1.Columns[2].Visible = false;
            dataGridView1.Columns[4].Visible = false;

            if (DT1.Text == "Actual")
            { 
                // 5,8,10,15 visible		6,7,9,11,12,13,14 not visible	
                dataGridView1.Columns[5].Visible = true;
                dataGridView1.Columns[6].Visible = true;
                dataGridView1.Columns[8].Visible = true;
                dataGridView1.Columns[10].Visible = true;
                dataGridView1.Columns[13].Visible = true;
                dataGridView1.Columns[15].Visible = true;
               
               dataGridView1.Columns[7].Visible = false;
                dataGridView1.Columns[9].Visible = false;
                dataGridView1.Columns[10].Visible = false;
                dataGridView1.Columns[11].Visible = false;
                dataGridView1.Columns[12].Visible = false;
                dataGridView1.Columns[14].Visible = false;

                dataGridView1.Columns[16].Visible = false;
                dataGridView1.Columns[17].Visible = false;
                dataGridView1.Columns[18].Visible = false;

                chart1.Series.Clear();
                chart1.Series.Add(ds9.Tables[0].Columns[5].ToString());
                chart1.Series.Add(ds9.Tables[0].Columns[8].ToString());
                chart1.Series.Add(ds9.Tables[0].Columns[10].ToString());
                chart1.Series.Add(ds9.Tables[0].Columns[13].ToString());
                chart1.Series.Add(ds9.Tables[0].Columns[15].ToString());
                chart1.Series[0].ChartType = SeriesChartType.Bar; chart1.Series[1].ChartType = SeriesChartType.Bar;
                chart1.Series[2].ChartType = SeriesChartType.Bar; chart1.Series[3].ChartType = SeriesChartType.Bar;
                chart1.Series[4].ChartType = SeriesChartType.Bar;

                chart1.Series[0].XValueMember = "Product_Type";
                chart1.Series[0].YValueMembers = "CC_Volume";
                chart1.Series[1].XValueMember = "Product_Type";
                chart1.Series[1].YValueMembers = "WE_Volume";
                chart1.Series[2].XValueMember = "Product_Type";
                chart1.Series[2].YValueMembers = "ME_Volume";
                chart1.Series[3].XValueMember = "Product_Type";
                chart1.Series[3].YValueMembers = "YE_Volume";
                chart1.Series[4].XValueMember = "Product_Type";
                chart1.Series[4].YValueMembers = "YTD_Growth";
                chart1.DataBind();

            }

            if (DT1.Text == "Change")
            {
                // 5,6,7,8,9,10,11,12,13,14,15 not visible			5-6 ,5-8, 5-13 difference	

                dataGridView1.Columns[16].Visible = true;
                dataGridView1.Columns[17].Visible = true;
                dataGridView1.Columns[18].Visible = true;

                dataGridView1.Columns[5].Visible = false;
                dataGridView1.Columns[6].Visible = false;
                dataGridView1.Columns[7].Visible = false;
                dataGridView1.Columns[8].Visible = false;
                dataGridView1.Columns[9].Visible = false;
                dataGridView1.Columns[10].Visible = false;
                dataGridView1.Columns[11].Visible = false;
                dataGridView1.Columns[12].Visible = false;
                dataGridView1.Columns[13].Visible = false;
                dataGridView1.Columns[14].Visible = false;
                dataGridView1.Columns[15].Visible = false;
                chart1.Series.Clear();
                chart1.Series.Add(ds9.Tables[0].Columns[16].ToString());
                chart1.Series.Add(ds9.Tables[0].Columns[17].ToString());
                chart1.Series.Add(ds9.Tables[0].Columns[18].ToString());

                chart1.Series[0].ChartType = SeriesChartType.Bar; chart1.Series[1].ChartType = SeriesChartType.Bar;
                chart1.Series[2].ChartType = SeriesChartType.Bar; 

                chart1.Series[0].XValueMember = "Product_Type";
                chart1.Series[0].YValueMembers = "'CC-PD_Volume'";

                chart1.Series[1].YValueMembers = "Product_Type";
                chart1.Series[1].YValueMembers = "'CC-WE_Volume'";

                chart1.Series[2].YValueMembers = "Product_Type";
                chart1.Series[2].YValueMembers = "'CC-YE_Volume'";

                chart1.DataBind();

            }

            if (DT1.Text == "Growth")
            { 
                // 7,9,11,15 visible		5,6,8,10,12,13,14 no visible	
                dataGridView1.Columns[7].Visible = true;
                dataGridView1.Columns[9].Visible = true;
                dataGridView1.Columns[11].Visible = true;
                dataGridView1.Columns[15].Visible = true;

                dataGridView1.Columns[5].Visible = false;
                dataGridView1.Columns[6].Visible = false;
                dataGridView1.Columns[8].Visible = false;
                dataGridView1.Columns[10].Visible = false;
                dataGridView1.Columns[12].Visible = false;
                dataGridView1.Columns[13].Visible = false;
              
                dataGridView1.Columns[16].Visible = false;
                dataGridView1.Columns[17].Visible = false;
                dataGridView1.Columns[18].Visible = false;
                chart1.Series.Clear();
                chart1.Series.Add(ds9.Tables[0].Columns[7].ToString());
                chart1.Series.Add(ds9.Tables[0].Columns[9].ToString());
                chart1.Series.Add(ds9.Tables[0].Columns[11].ToString());
                chart1.Series.Add(ds9.Tables[0].Columns[15].ToString());

                chart1.Series[0].ChartType = SeriesChartType.Bar; chart1.Series[1].ChartType = SeriesChartType.Bar;
                chart1.Series[2].ChartType = SeriesChartType.Bar; chart1.Series[3].ChartType = SeriesChartType.Bar;
                

                chart1.Series[0].XValueMember = "Product_Type";
                chart1.Series[0].YValueMembers = "CC_Percent";
                chart1.Series[1].XValueMember = "Product_Type";
                chart1.Series[1].YValueMembers = "WE_Percent";
                chart1.Series[2].XValueMember = "Product_Type";
                chart1.Series[2].YValueMembers = "ME_Percent";
                chart1.Series[3].XValueMember = "Product_Type";
                chart1.Series[3].YValueMembers = "YTD_Growth";


                chart1.DataBind();

            }

            dataGridView1.SelectionMode = DataGridViewSelectionMode.FullRowSelect;


        }

        private void button1_Click(object sender, EventArgs e)
        {
            datagrid_update();
        }

    
    }
}
